# -*- coding: utf-8 -*-
"""
Created on Thu Jun  7 15:25:36 2018

@author: xuchao
"""
#%%
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签
plt.rcParams['axes.unicode_minus']=False #用来正常显示负号
import datetime
from datetime import datetime
import pymysql
import json
import os
os.chdir('./')
now = datetime.strftime(datetime.now(),'%Y-%m-%d')
filename='客户期末时点还款数据%s.xlsx'%now
if os.path.exists(filename):
    os.remove(filename)
from sqlalchemy import create_engine
writer = pd.ExcelWriter(filename)
engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/qy_datasys?charset=utf8', echo=False)

start_time = datetime.strftime(datetime.now(),'%Y-%m-%d %H:%M:%S')


sql1 = '''
SELECT
	user_name '客户姓名',
	contract_id '合同ID',
	user_id_num '身份证号',
	loan_amount / 100 '放款金额',
	loan_term '分期期数',
	DATE_FORMAT(loan_at, '%Y-%m-%d') '放款时间',
	DATE_FORMAT(loan_at, '%Y-%m') '放款月份',
	term '期数',
	DATE_FORMAT(repay_date, '%Y-%m-%d') '计划还款时间',
	DATE_FORMAT(repay_date, '%Y-%m') '计划还款月份',
	pay_ben_jin / 100 '应还本金',
	pay_loan_interest / 100 '应还利息',
	pay_platform_interest / 100 '应还平台服务费',
	counted_should_pay / 100 '应还总额',
	pay_platform_service / 100 '应还放款手续费',
	STATUS ,
	overdue_status ,
	DATE_FORMAT(end_date, '%Y-%m-%d') '实际还款时间',
	DATE_FORMAT(end_date, '%Y-%m') '实还月份',
	already_pay_ben_jin / 100 '实还本金',
	already_pay_loan_interest / 100 '实还利息',
	already_pay_fund_side_service / 100 '实还管理费',
	already_pay_platform_interest / 100 '实还平台服务费',
	already_pay_overdue_amount / 100 '实还罚息',
	early_settled_handle_charge / 100 '实还提前还款手续费'
FROM
	qy_repayment_prod.repay_plans
ORDER BY
	loan_at,
	contract_id,
	term;
'''


sql2 = '''

SELECT
	jinjian_user_name '客户姓名',
	user_id_num '身份证号',
	contract_id '合同ID',
	show_id,
	jinjian_id,
	agency_name '中介机构',
	agency_employee '渠道经理',
	inter_view_trail_name '面签员',
	first_trail_name '初审员',
	re_trail_name '终审员',
	quantization_point,
	fund_side '资金方',
	risk_param,
	YEAR (commit_time) -
IF (
	length(user_id_num) = 18,
	substring(user_id_num, 7, 4),

IF (
	length(user_id_num) = 15,
	concat(
		'19',
		substring(user_id_num, 7, 2)
	),
	NULL
)
) AS 年龄,
 CASE
IF (
	length(user_id_num) = 18,
	cast(
		substring(user_id_num, 17, 1) AS UNSIGNED
	) % 2,

IF (
	length(user_id_num) = 15,
	cast(
		substring(user_id_num, 15, 1) AS UNSIGNED
	) % 2,
	3
)
)
WHEN 1 THEN
	'男'
WHEN 0 THEN
	'女'
ELSE
	'未知'
END AS 性别,
inter_view
FROM
	qy_approval_prod.approval_orders
WHERE
	operator_status = '放款成功'
ORDER BY
	loan_at,contract_id
'''

try:
    conn = pymysql.connect(host='172.16.1.90', port=3306,
                           user='lianghua', passwd='xc123',
                           charset='UTF8')
    with conn.cursor() as cur:
        cur.execute(sql1)        
        df1 = pd.read_sql(sql1,conn)
        cur.execute(sql2)
        df2 = pd.read_sql(sql2,conn)
        
finally:
    cur.close()
    conn.close()

df2_a1dir = {}#计算df2中进件方案
df2_a2dir = {}#计算df2启元评分分类
df2_a3dir = {}#计算df2年龄分类
for x in df2.index:
# =============================================================================
    if df2['inter_view'].iloc[x]!='':
        if json.loads(df2['inter_view'].iloc[x])['jinjian_plan']!='':
            d = json.loads(json.loads(df2['inter_view'].iloc[x])['jinjian_plan'])
            if type(d) is dict:
                g = d['name']
            if type(d) is list:
                g = d[0]['name']
            df2_a1dir[x] = g[:3]


# =============================================================================
    if df2['quantization_point'].iloc[x]<400:
        df2_a2dir[x] = '(0,400)'
    elif df2['quantization_point'].iloc[x]<450:
        df2_a2dir[x] = '[400,450)'
    elif df2['quantization_point'].iloc[x]<500:
        df2_a2dir[x] = '[450,500)'
    elif df2['quantization_point'].iloc[x]<550:
        df2_a2dir[x] = '[500,550)'
    elif df2['quantization_point'].iloc[x]<650:
        df2_a2dir[x] = '[550,650)'
    else:
        df2_a2dir[x] = '[650,+)'
# =============================================================================
   

        
    if df2['年龄'].iloc[x]<35:
        df2_a3dir[x] = '(18,35)'
    elif df2['年龄'].iloc[x]<45:
        df2_a3dir[x] = '[35,45)'
    elif df2['年龄'].iloc[x]<55:
        df2_a3dir[x] = '[45,55)'
    else:
        df2_a3dir[x] = '[55,+)'


# =============================================================================
df2['进件方案'] = pd.Series(df2_a1dir)
df2['评分分类'] = pd.Series(df2_a2dir)
df2['年龄分类'] = pd.Series(df2_a3dir)


# =============================================================================
    
# =============================================================================
# 
df2['中介机构'].loc[(df2['中介机构']=='内部测试用')|(df2['中介机构']=='启元优贷')]='启元优享'
df2['中介机构'].loc[(df2['中介机构']=='诚汇通')|(df2['中介机构']=='中汇盛世')]='诚汇通/中汇盛世'
df2['中介机构'].loc[(df2['中介机构']=='旺钛企业管理咨询(上海)有限公司')]='钛旺企业管理咨询(上海)有限公司'
df2['中介机构'].loc[(df2['中介机构']=='三德资产管理')|(df2['中介机构']=='中商银汇')
                        |(df2['中介机构']=='前元金服')|(df2['中介机构']=='新域实业')
                        |(df2['中介机构']=='新星名车')|(df2['中介机构']=='欧垺资本')
                        |(df2['中介机构']=='永捷担保')|(df2['中介机构']=='洋鑫梦投资')
                        |(df2['中介机构']=='蒲公英')|(df2['中介机构']=='辉翔金服')
                        |(df2['中介机构']=='鑫至尊')|(df2['中介机构']=='顶呱呱')
                        |(df2['中介机构']=='鹏升金融')]='其它'





df2.loc[(df2['中介机构']=='诚汇通/中汇盛世')|(df2['中介机构']=='深圳前海神马金融服务有限公司')|
        (df2['中介机构']=='深圳英格玛商务咨询有限公司')|(df2['中介机构']=='有道咨询服')|(df2['中介机构']=='其它') ,'地区']='深圳'



df2.loc[(df2['中介机构']=='广州银沃投资咨询有限公司')|(df2['中介机构']=='广州联晟商业信息咨询有限公司')|
        (df2['中介机构']=='广州神马'),'地区']='广州'

df2.loc[(df2['中介机构']=='福建钜鸿投资咨询有限公司')|(df2['中介机构']=='福建海西网络科技有限公司'),'地区']='福建'
        

df2.loc[(df2['中介机构']=='上海牧牛资产管理有限公司')|(df2['中介机构']=='钛旺企业管理咨询(上海)有限公司')
        |(df2['中介机构']=='望壕金融信息服务(上海)有限公司'),'地区']='上海'


df2.loc[(df2['中介机构']=='启元优享'),'地区']='启元'       
# =============================================================================


#df_cus = df1.loc[df1['客户姓名']=='韩东']#'测试客户'
def cal(df_cus):
    d2 = []
    df_cus['实际还款时间'].fillna('2099-09-09',inplace=True)####填充空白地方，以便进行时间的比较
    #df_cus = df_cus.loc[df_cus['计划还款时间']<=now]###############为了满足赖总的需求，而考虑时间因素的影响而去掉##1
    for j in list(set(df_cus['期数'].loc[((df_cus['STATUS']=='payed')|(df_cus['STATUS']=='overdue_payed')|(df_cus['STATUS']=='early_settled')|(df_cus['STATUS']=='overdue'))])):
        #print(j)
        df_cus_1 = df_cus.loc[df_cus['期数']<=j].reset_index(drop=True)
        df_cus_1['query_date'] = df_cus_1['计划还款时间'].max()
        d1 = {}#用来重新记录当时的还款状态
        for i in df_cus_1.index:
            
            if df_cus_1['计划还款时间'].iloc[i]>df_cus_1['实际还款时间'].iloc[i]:
                d1[i] = 'early_settled'#'early_settled'是否调整为paid
            elif df_cus_1['计划还款时间'].iloc[i]==df_cus_1['实际还款时间'].iloc[i]:
                d1[i] = 'paid'#'正常还款'
            else:
                #取最大的期数的那一期的应还款时间作为对比
                if df_cus_1['query_date'].iloc[i]<df_cus_1['实际还款时间'].iloc[i]:
                    d1[i] = 'overdue'
                else:
                    d1[i] = 'paid'
        
        df_cus_1['还款状态'] = pd.Series(d1)
        a = df_cus_1.loc[(df_cus_1['还款状态']=='overdue')]
        if len(a)==0:
            df_cus_1['逾期状态']='Normal'
        elif len(a)<=6:
            df_cus_1['逾期状态']='M'+(str(len(a)))
        else:
            df_cus_1['逾期状态']='M6+'
            
            
        if len(df_cus_1.loc[(df_cus_1['还款状态']=='paid')|(df_cus_1['还款状态']=='early_settled')])>0:
            df_cus_1['累计已还本金'] = df_cus_1['实还本金'].loc[(df_cus_1['还款状态']=='paid')|(df_cus_1['还款状态']=='early_settled')].sum()
        else:
            df_cus_1['累计已还本金'] = 0    
        df_cus_1['剩余未还本金'] = df_cus_1['放款金额'] - df_cus_1['累计已还本金']
        #df_cus_1['实际还款时间'].loc[df_cus_1['实际还款时间']=='2099-09-09']=None
        df_cus_1['实际还款时间'].replace('2099-09-09', None, inplace=True)
        d2.append(df_cus_1)
    
        
    d3 = []
    for i in range(len(d2)):
        #print(i)
        d3.append(d2[i].drop_duplicates(['客户姓名','合同ID'],keep='last'))
    df_fin = pd.concat(d3).sort_values(by=['合同ID','期数']).reset_index(drop=True) 
    
    
    
# =============================================================================
#处理提前结清客户数据：  

    if ('early_settled' in set(df_fin['STATUS'])):
    #if ('early_settled' in set(df_fin['还款状态']))&(df_fin['剩余未还本金'].iloc[-1]<=0.1):
        a = df_fin['期数'].loc[df_fin['STATUS']=='early_settled'].min()#####拿出第一笔提前还款的那一期
        b = df_fin['实还本金'].loc[df_fin['期数']>=a].sum()#################计算全部提前还款的本金
        c = df_fin['剩余未还本金'].loc[df_fin['期数']==(a-1)].min()#########拿出提前还款前的那一期的剩余未还本金
        df_fin = df_fin.loc[df_fin['期数']<=a]
        df_fin['实还本金'].loc[df_fin['期数']==a] = b
        df_fin['累计已还本金'].loc[df_fin['期数']==a] = df_fin['实还本金'].loc[df_fin['期数']<=a].sum()
        df_fin['剩余未还本金'].loc[df_fin['期数']==a] = df_fin['放款金额'].loc[df_fin['期数']==a] - df_fin['累计已还本金'].loc[df_fin['期数']==a]####或者直接用c代替也可
# =============================================================================   
    df_fin['还款状态'].loc[(df_fin['还款状态']=='early_settled')&(df_fin['剩余未还本金']>=0.1)]='Partial_early_settled'
    df_fin['还款状态'].loc[(df_fin['还款状态']=='early_settled')&(df_fin['剩余未还本金']<0.1)]='All_early_settled'

    return df_fin



#now = datetime.strftime(datetime.now(),'%Y-%m-%d')
d4 = []########################################添加每一个客户的每期期末还款的DataFrame到d4中
for i in set(df1['合同ID'].loc[(df1['期数']==1)&(df1['计划还款时间']<=now)]):
    
    df_cus = df1.loc[df1['合同ID']==i]
    #print(df_cus.客户姓名)
    df_fin = cal(df_cus)
    d4.append(df_fin)
   
df_all = pd.concat(d4).sort_values(by=['放款时间','合同ID','期数']).reset_index(drop=True) 
df_all.to_excel(writer,sheet_name='客户期末时点还款数据')

alist = ['客户姓名', '合同ID', '身份证号']
for i in alist:
    #print(i)
    df_all[i] = df_all[i].map(lambda s : s.strip(' '))
    df2[i] = df2[i].map(lambda s : s.strip(' '))
    
    

df_all_info = pd.merge(df2.drop(['quantization_point', 'risk_param', '年龄','inter_view'],axis=1),
                  df_all.drop(['放款月份','计划还款月份','应还利息', '应还平台服务费', '应还总额','应还放款手续费',
                               'STATUS', 'overdue_status','实还月份'],axis=1),on=['客户姓名', '合同ID', '身份证号'],how='inner')  
df2.to_excel(writer,sheet_name='客户基础信息')
df_all_info['update'] = datetime.strftime(datetime.now(),'%Y-%m-%d %H:%M:%S')
df_all_info = df_all_info.set_index('update').reset_index()
df_all_info.to_excel(writer,sheet_name='合并数据')   


end_time = datetime.strftime(datetime.now(),'%Y-%m-%d %H:%M:%S')
print('开始时间:',start_time)
print('结束时间:',end_time)
print(len(df_all_info))

#writer.save()
#df_all_info.pivot_table(index='逾期状态',columns='期数',values='客户姓名',aggfunc=len,margins=True)
df_all_info.to_sql('repay_ment',con = engine,if_exists = 'replace',index=False)

